﻿using System;
using System.Collections.Generic;
using System.Text;
using Model = SQLServerHealthCheck.Model;
using SQLServerHealthCheck.Model;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using SMO = Microsoft.SqlServer.Management.Smo;
using WMI = Microsoft.SqlServer.Management.Smo.Wmi;
using System.Windows.Forms;
using System.IO;
using System.Collections;
using System.Data.SqlTypes;


namespace SQLServerHealthCheck.SQLServer
{
  

    public class CollectSecurityData
    {

        List<Exception> errors;
        HealthCheckDataContext dc;
        SQLServerInstance sqlServerInstance;

        public List<Exception> Collect(SQLServerInstance SqlServerInstance)
        {
            errors = new List<Exception>();
            try
            {
                sqlServerInstance = SqlServerInstance;

                dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"].ToString());

                FillLogins();
                FillUsers();
                FillServerRoles();
                FillDatabaseUserRoles();
            }

            catch (System.Security.SecurityException ex1)
            {
                MessageBox.Show("You don't have permission to delete file in path " + Application.StartupPath, "HealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }




            return errors;
        }

        public void FillLogins()
        {

            try
            {
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_DatabaseCheckLog.txt", "FillDatabasesAndFiles: ", " FillDatabasesAndFiles method in DatabaseCheck Started for " + service.InstanceName.ToString());

                //Check the connection to SQL Server Instance and gets the details.

                String[] parts = sqlServerInstance.InstanceName.ToLower().Split('\\');
                String serverName = parts[0];


                Server server = dc.Servers.FirstOrDefault(s => s.Name == serverName);

                if (server != null)
                {

                    Service service = server.Services.FirstOrDefault(s => s.InstanceName == sqlServerInstance.InstanceName);
                    if (service != null)
                    {

                        if (CollectServerAndServiceData.IsSQLServerAccessible(sqlServerInstance.GetConnectionString("master")))
                        {
                            using (SqlConnection cn = new SqlConnection(sqlServerInstance.GetConnectionString("master")))
                            {
                                cn.Open();
                                String sql = @"select name,sid,create_date,is_disabled

                                                   from master.sys.server_principals";

                                SqlCommand cmd = new SqlCommand(sql, cn);
                                SqlDataReader rd = cmd.ExecuteReader();

                                Login l = null;

                                while (rd.Read())
                                {



                                    l = new Login();


                                    l.Name = rd.IsDBNull(0) ? "" : rd[0].ToString();

                                    SqlBinary sqlBinary = rd.GetSqlBinary(1);

                                    if (!sqlBinary.IsNull)
                                    {

                                        l.SID = sqlBinary.Value;
                                    }

                                    l.CreateDate = rd.IsDBNull(2) ? (DateTime?)null : rd.GetDateTime(2);
                                    l.Enabled = rd.GetBoolean(3);

                                    service.Logins.Add(l);

                                }

                                rd.Close();


                            }

                            dc.SubmitChanges();

                            //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_DatabaseCheckLog.txt", "FillDatabasesAndFiles: ", " FillDatabasesAndFiles method in DatabaseCheck Completed for " + service.InstanceName.ToString());

                        }





                    }




                }
            }
            catch (Exception ex)
            {

                errors.Add(ex);
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_DatabaseCheckLog.txt", "FillDatabasesAndFiles: ", " Some error occured in Database Check.Please check exception file for details");
            }



        }

        public void FillUsers()
        {
            try
            {

                String[] parts = sqlServerInstance.InstanceName.ToLower().Split('\\');
                String serverName = parts[0];


                Server server = dc.Servers.FirstOrDefault(s => s.Name == serverName);

                if (server != null)
                {

                    Service service = server.Services.FirstOrDefault(s => s.InstanceName == sqlServerInstance.InstanceName);
                    if (service != null)
                    {

                        if (CollectServerAndServiceData.IsSQLServerAccessible(sqlServerInstance.GetConnectionString("master")))
                        {
                            using (SqlConnection cn = new SqlConnection(sqlServerInstance.GetConnectionString("master")))
                            {
                                cn.Open();
                                String sql = @"DECLARE @Database nvarchar(50)                                                    
                                                    
                                                    CREATE TABLE #DatabaseUsers
                                                    (
                                                       DatabaseName VARCHAR(100),
                                                       Name VARCHAR(50),
                                                       SID  VARBINARY(100),
                                                       CreateDate DATETIME,
                                                       DefaultScheme VARCHAR(50))



                                                    DECLARE GetDatabaseName CURSOR 
                                                    FOR
                                                    
                                                    SELECT d.name FROM sys.databases d 

                                                    
                                                    OPEN GetDatabaseName

                                                    FETCH NEXT FROM GetDatabaseName INTO @Database

                                                    WHILE @@FETCH_STATUS = 0

	                                                    BEGIN
	
	                                                    EXECUTE('
	                                                    INSERT #DatabaseUsers
	                                                    (
		                                                    [DatabaseName],
		                                                    Name ,
		                                                    SID  ,
		                                                    CreateDate ,
		                                                    DefaultScheme
	                                                    )
	                                                    SELECT 
		                                                    ''' + @Database + ''' ,
		                                                    name, 
		                                                    sid, 
		                                                    create_date, 
		                                                    default_schema_name 
	                                                    FROM 
		                                                    [' +@Database+ '].sys.database_principals WHERE type =''S'' OR type = ''U'' ')
	

	                                                    FETCH NEXT FROM GetDatabaseName INTO @Database
                                                    END
                                                    CLOSE GetDatabaseName
                                                    DEALLOCATE GetDatabaseName

                                                    select *
                                                    from #DatabaseUsers

                                                    DROP TABLE #DatabaseUsers";

                                SqlCommand cmd = new SqlCommand(sql, cn);
                                SqlDataReader rd = cmd.ExecuteReader();



                                while (rd.Read())
                                {

                                    // Create New DatabaseUser and get DatabaseUserId
                                    DatabaseUser DbUser = null;

                                    DbUser = new DatabaseUser();
                                    DbUser.Name = rd.IsDBNull(1) ? "" : rd[1].ToString();


                                    SqlBinary sqlBinary = rd.GetSqlBinary(2);

                                    if (!sqlBinary.IsNull)
                                    {
                                        DbUser.SID = sqlBinary.Value;
                                    }

                                    DbUser.CreateDate = rd.IsDBNull(3) ? (DateTime?)null : rd.GetDateTime(3);
                                    DbUser.DefaultSchema = rd.IsDBNull(4) ? "" : rd[4].ToString();

                                    string databaseName = rd[0].ToString();

                                    Database database = service.Databases.FirstOrDefault(d => d.DatabaseName == databaseName);

                                    if (database != null)
                                    {
                                        DbUser.DatabaseID = database.DatabaseID;
                                    }

                                    database.DatabaseUsers.Add(DbUser);

                                }

                                rd.Close();


                            }

                            dc.SubmitChanges();

                            //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_DatabaseCheckLog.txt", "FillDatabasesAndFiles: ", " FillDatabasesAndFiles method in DatabaseCheck Completed for " + service.InstanceName.ToString());

                        }





                    }




                }
            }
            catch (Exception ex)
            {

                errors.Add(ex);
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_DatabaseCheckLog.txt", "FillDatabasesAndFiles: ", " Some error occured in Database Check.Please check exception file for details");
            }



        }

        public void FillServerRoles()
        {
            try
            {
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_DatabaseCheckLog.txt", "FillDatabasesAndFiles: ", " FillDatabasesAndFiles method in DatabaseCheck Started for " + service.InstanceName.ToString());

                //Check the connection to SQL Server Instance and gets the details.

                String[] parts = sqlServerInstance.InstanceName.ToLower().Split('\\');
                String serverName = parts[0];


                Server server = dc.Servers.FirstOrDefault(s => s.Name == serverName);

                if (server != null)
                {

                    Service service = server.Services.FirstOrDefault(s => s.InstanceName == sqlServerInstance.InstanceName);
                    if (service != null)
                    {

                        if (CollectServerAndServiceData.IsSQLServerAccessible(sqlServerInstance.GetConnectionString("master")))
                        {
                            using (SqlConnection cn = new SqlConnection(sqlServerInstance.GetConnectionString("master")))
                            {
                                cn.Open();
                                String sql = @"SELECT 
	                                                sp1.name AS 'Login Name',
	                                                sp2.name AS 'Server Role Name'
                                                FROM 
	                                                sys.server_principals sp1
		                                                INNER JOIN 
	                                                sys.server_role_members srm ON sp1.principal_id = srm.member_principal_id
		                                                INNER JOIN 
	                                                sys.server_principals sp2 ON sp2.principal_id = srm.role_principal_id
                                                ORDER BY
                                                    sp1.Name";

                                SqlCommand cmd = new SqlCommand(sql, cn);
                                SqlDataReader rd = cmd.ExecuteReader();

                                LoginServerRole lsr = null;
                                String previousLoginName = "";
                                Login login = null;

                                
                                while (rd.Read())
                                {
                                    //Create New LoginServerRole Database Table object

                                    lsr = new LoginServerRole();

                                    string LoginName = rd[0].ToString();

                                    if (previousLoginName == "" || LoginName != previousLoginName)
                                    {
                                        login = service.Logins.FirstOrDefault(l => l.Name == LoginName);
                                    }

                                    if (login != null)
                                    {
                                        string ServerRoleName = rd[1].ToString();

                                        ServerRole sr = dc.ServerRoles.FirstOrDefault(s => s.Name == ServerRoleName);

                                        if (sr != null)
                                        {
                                            lsr.ServerRole = sr;

                                            login.LoginServerRoles.Add(lsr);

                                        }
                                    }

                                    previousLoginName = LoginName;
                                }

                                rd.Close();


                            }

                            dc.SubmitChanges();

                            //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_DatabaseCheckLog.txt", "FillDatabasesAndFiles: ", " FillDatabasesAndFiles method in DatabaseCheck Completed for " + service.InstanceName.ToString());

                        }





                    }




                }
            }
            catch (Exception ex)
            {

                errors.Add(ex);
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_DatabaseCheckLog.txt", "FillDatabasesAndFiles: ", " Some error occured in Database Check.Please check exception file for details");
            }
        }

        public void FillDatabaseUserRoles()
        {
            try
            {
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_DatabaseCheckLog.txt", "FillDatabasesAndFiles: ", " FillDatabasesAndFiles method in DatabaseCheck Started for " + service.InstanceName.ToString());

                //Check the connection to SQL Server Instance and gets the details.

                String[] parts = sqlServerInstance.InstanceName.ToLower().Split('\\');
                String serverName = parts[0];


                Server server = dc.Servers.FirstOrDefault(s => s.Name == serverName);

                if (server != null)
                {

                    Service service = server.Services.FirstOrDefault(s => s.InstanceName == sqlServerInstance.InstanceName);
                    if (service != null)
                    {

                        if (CollectServerAndServiceData.IsSQLServerAccessible(sqlServerInstance.GetConnectionString("master")))
                        {
                            using (SqlConnection cn = new SqlConnection(sqlServerInstance.GetConnectionString("master")))
                            {
                                cn.Open();
                                String sql = @"DECLARE @Database nvarchar(50)                                                    
                                                    
                                                            CREATE TABLE #DatabaseUsersRoles
                                                            (
                                                               DatabaseName VARCHAR(100),
                                                               DatabaseUser_Name VARCHAR(50),
                                                               DatabaseUser_Role VARCHAR(50)
                                                               )

                                                            DECLARE GetDatabaseName CURSOR 
                                                            FOR
    
                                                            SELECT d.name FROM sys.databases d 

    
                                                            OPEN GetDatabaseName

                                                            FETCH NEXT FROM GetDatabaseName INTO @Database

                                                            WHILE @@FETCH_STATUS = 0

                                                                BEGIN

                                                                EXECUTE('
                                                                INSERT #DatabaseUsersRoles
                                                                (
                                                                    [DatabaseName],
                                                                    DatabaseUser_Name,
                                                                    DatabaseUser_Role 
            
                                                                )
                                                                SELECT 
                                                                    ''' + @Database + ''' AS DatabaseName,
                                                                    mp.name AS database_user,
                                                                    rp.name AS database_role 
			 
           
                                                                FROM 
                                                                    [' +@Database+ '].sys.database_role_members drm 
							                                                        join sys.database_principals rp on drm.role_principal_id = rp.principal_id
							                                                        join sys.database_principals mp on drm.member_principal_id = mp.principal_id
                                                                                    WHERE rp.type =''R''
                                                                                    ORDER BY DatabaseName,database_user,database_role')


                                                                                        FETCH NEXT FROM GetDatabaseName INTO @Database
                                                                                    END
                                                                                    CLOSE GetDatabaseName
                                                                                    DEALLOCATE GetDatabaseName

                                                                                    select *
                                                                                    from #DatabaseUsersRoles

                                                                                   DROP TABLE #DatabaseUsersRoles
                                                                                                                 ";

                                SqlCommand cmd = new SqlCommand(sql, cn);
                                SqlDataReader rd = cmd.ExecuteReader();

                                DatabaseUserRole dur = null;

                               
                                
                                DatabaseUser databaseUser = null;

                                 //DatabaseUserRole previousDatabaseRole;

                                while (rd.Read())
                                {
                                    dur = new DatabaseUserRole();  
                                    string databaseName = rd[0].ToString();

                                    Database database = service.Databases.FirstOrDefault(d => d.DatabaseName == databaseName);

                                    if (database!=null)
                                    {
                                    //Create New LoginServerRole Database Table object

                                    
                                    //databaseUser = new DatabaseUser();

                                    string dbUser = rd[1].ToString();

                                   
                                        
                                        databaseUser = database.DatabaseUsers.FirstOrDefault(l => l.Name == dbUser);
                                  

                                    if (databaseUser != null)
                                    {
                                        
                                        
                                        string DatabaseRoleName = rd[2].ToString();

                                        DatabaseRole dr = dc.DatabaseRoles.FirstOrDefault(r => r.Name == DatabaseRoleName);

                                        
                                        
                                        if (dr != null)
                                        {

                                                                                  
                                            dur.DatabaseRole = dr;                                           
                                                                                                                    
                                                                                         
                                            databaseUser.DatabaseUserRoles.Add(dur);
                                              
                                            dc.SubmitChanges();

                                                                                  
                                            
                                            

                                        }
                                    }
                                    
                                  
                                    }

                                    
                                    
                                }


                                rd.Close();

                            }
                            
                            dc.SubmitChanges();

                            //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_DatabaseCheckLog.txt", "FillDatabasesAndFiles: ", " FillDatabasesAndFiles method in DatabaseCheck Completed for " + service.InstanceName.ToString());

                        }





                    }




                }
            }
            catch (Exception ex)
            {

                errors.Add(ex);
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_DatabaseCheckLog.txt", "FillDatabasesAndFiles: ", " Some error occured in Database Check.Please check exception file for details");
            }
        }
        
    }
}


